Class MySQLCursor

It is used to send a sql statement to database.
MySQL cursor is read-only and non-scrollable.
Read-only
- You cannot update data in the underlying table through the cursor.
Non-scrollable
- You can only fetch rows in the order determined by the SELECT statement.
- You cannot fetch rows in the reversed order.
- You cannot skip rows or jump to a specific row in the result set.

Constructor
MySQLCursor(MySQLConnection )	Creates a cursor from a connection object.

Methods
execute(query, params=None)	This method executes the given query or command. For insert / update / delete operation we can check how many rows affected by using cursor property rowcount. The params argument is used to pass parameter values of the query.
fetchone( )	This method return one single record as list.
fetchall()	This method return all records as multidimensional list.
fetchmany(SIZE)	This method return specific number of records.

Properties
rowcount	Indicates no of rows affected
with_rows	returns True or False to indicate whether the most recently executed operation produced rows.

#WAP to insert a record into table accmaster. Database=ccitdb user=root password=admin.

import mysql.connector as sql
try:
  mydb=sql.MySQLConnection(user="root",password="admin", 
  database="ccitdb")
  smt="insert into accmaster values(1010,'Raj Joshi',25000)"
  cur=mydb.cursor()
  cur.execute(smt)
  if cur.rowcount==1:
    print("Record Inserted...")
    mydb.commit()
  mydb.close()
except Error as e:
  print("Failed to Connect",e)

#WAP to delete a record from table accmaster.database=ccitdb user=root password=admin.

import mysql.connector as sql
try: 
  mydb=sql.MySQLConnection(user="root",password="admin" ,database=" ccitdb")
  smt="delete from accmaster where accno=1010"
  cur=mydb.cursor()
  cur.execute(smt)
  if cur.rowcount==1:
    print("Record deleted...")
    mydb.commit()
  mydb.close()
except Exception as e:
  print("Failed to Connect",e)

#WAP to read accno, name and balance and add a record into accmaster table.

import mysql.connector as sql
try:
  an=input("Enter accno:")
  nm=input("Enter name:")
  amt=input("Enter amount:")
  cmd="insert into accmaster values("+an+",'"+nm+"',"+amt +")" 
  conn=sql.connect(database="ccitdb",user="root",password="admin")
  cur=conn.cursor()
  cur.execute(cmd)
  if cur.rowcount==1:
    print("Record Inserted..")
    conn.commit()
  conn.close()
except Exception as e:
  print("Error:",e)

Enter accno: 1004
Enter name: Mona Mantri
Enter amount: 10000
Record Inserted..

#WAP to read accno and amt and perform deposit operation on accmaster table. database=ccitdb user=root password=admin.

import mysql.connector as sql
try:
  an=input("Enter accno:")
  amt=input("Enter amount:") 
  conn=sql.connect(database="ccitdb",user="root",password= "admin")
  cmd="update accmaster set balance=balance+"+amt+" where accno="+an
  cur=conn.cursor()
  cur.execute(cmd)
  if cur.rowcount==1:
    print("amount deposited..")
    conn.commit()
  else:
    print("AccNo not found..")
  conn.close()
except Exception as e:
  print("Error:",e)

Enter accno: 1010
Enter amount: 7000
amount deposited

#WAP To read data and insert it into accmaster table.

import mysql.connector as sql
an=input("Enter AccNo: ")
nm=input("Enter Name: ")
bl=input("Enter Balance: ")
try:
  query="insert into accmaster values (%s,%s,%s)"
  conn=sql.connect(user="root",password="admin",database="ccitdb")
  cur=conn.cursor()
  lst=[an,nm,bl]
  cur.execute(query , lst)
  print("record inserted..")
  conn.commit()
  conn.close()
except Exception as e:
  print("Error:",e)

Enter accno : 1004
Enter name : Mona Mantri
Enter amount : 10000
Record Inserted..

#WAP To read accno and delete it from accmaster table.

import mysql.connector as sql
an=input("Enter accno: ")
try:
  query="delete from accmaster where accno=%s"
  conn=sql.connect(user="root",password="admin",database="ccitdb")
  cur=conn.cursor()
  lst=[an]
  cur.execute(query , lst)
  if cur.rowcount==1:
    print("Record Deleted...")
    conn.commit()
  conn.close()
except Exception as e:
  print("Error:",e)

Enter accno: 1010
Record Deleted...

#WAP to read accno and amt. Perform deposit operation on accmaster table

import mysql.connector as sql
an=input("Enter accno: ")
amt=input("Enter amount: ")
try:
  query="update accmaster set balance=balance + %s where 
  accno=%s"
  conn=sql.connect(user="root",password="admin",database="ccitdb")
  cur=conn.cursor()
  lst=[amt,an]
  cur.execute(query , lst)
  if cur.rowcount==1:
    print("amount deposited..")
    conn.commit()
  else:
    print("AccNo not found..")
  conn.close()
except Exception as e:
  print("Error:",e)
  print("record inserted..")

Enter accno: 1010
Enter amount: 7000
amount deposited

#WAP to read accno and display record of that account from accmaster table.

import mysql.connector as sql
an=input("Enter accno: ")
try:
  query="select name,balance from accmaster where accno=%s"
  conn=sql.connect(user="root",password="admin",database="ccitdb")
  cur=conn.cursor()
  lst=[an]
  cur.execute(query , lst)
  row=cur.fetchone()
  if row==None:
    print("AccNo. not Found")
  else:
    print("Name is",row[0])
    print("Balance is",row[1])
  conn.close()
except Exception as e:
  print("Error:",e)

Enter accno: 1010
Name is Raj Joshi
Balance is 25000

#WAP to display all records from accmaster table.

import mysql.connector as sql
try:
  query="select * from accmaster"
  conn=sql.connect(user="root",password="admin",database="ccitdb")
  cur=conn.cursor()
  cur.execute(query)
  rows=cur.fetchall()
  for row in rows:
    print(row[0],row[1],row[2])
  conn.close()
except Exception as e:
  print("Error:",e)

1003 Mona Mantri 16000
1004 Amar Agrawal 18500
1006 Mandar Joshi 50000
1007 Sanjay Rathi 25000
1010 Raj Joshi 25000

#WAP to read accno and amount and perform withdraw operation on account. (check if balance is available or not).

import mysql.connector as sql
try:
  an=input("Enter AccNo:")
  amt=input("Enter Amount:")
  conn=sql.connect(database="ccitdb",user="root",password="admin")
  cmd="select balance from accmaster where accno= %s"
  cur=conn.cursor()
  cur.execute(cmd,[an])
  row=cur.fetchone()
  if row != None :
    bal=row[0]
    if bal>=int(amt):
      cmd="update accmaster set balance=balance - %s where 
      accno= %s"
      curx=conn.cursor()
      curx.execute(cmd,[amt,an] )
      conn.commit()
      print("Amount withdrawn...")
    else:
      print("Insufficient Balance ",bal)
  else:
    print("AccNo Not Found..")
  conn.close()
except Exception as e:
  print("Error:",e)

Enter Accno: 1010
Enter Amount: 7000
Amount Withdrawn

#WAP to read job and find list of empnames from emp table for that job

import mysql.connector as sql
try:
  jb=input("Enter Job:")
  conn=sql.connect(database="cmpdb",user="root",password="admin")
  cmd="select name from emp where job= %s"
  cur=conn.cursor()
  cur.execute(cmd ,[ jb ])
  rows=cur.fetchall()
  for row in rows:
    print(row[0])
  conn.close()
except Exception as e:
  print("Error:",e)

Enter Job: Clerk
Gopal Pandey
Raja Rathi
Rajev Ranjan

#WAP to read empno and amt. Increment salary of that emp by specified amt

import mysql.connector as sql
try:
  en=input("Enter EmpNo:")
  amt=input("Enter Increment Amt:")
  conn=sql.connect(database="cmpdb",user="root",password="admin")
  cmd="update emp set salary=salary+ %s where empno= %s"
  cur=conn.cursor()
  cur.execute(cmd ,[amt,en])
  if cur.rowcount==1:
    print("Done..")
    conn.commit()
  else:
    print("EmpNo Not Found..")
except Exception as e:
  print("Error:",e)
finally:
  conn.close()

Enter EmpNo: 1006
Enter Increment Amt: 7000
Done...

#WAP to read name, job, salary and deptno. Insert a record into emp table.

import mysql.connector as sql
try:
  nm=input("Enter Name:")
  jb=input("Enter Job:")
  sl=input("Enter Salary:")
  dn=input("Enter Deptno:")
  conn=sql.connect(database="cmpdb",user="root",password="admin")
  cmd="select max(empno) from emp"
  cur=conn.cursor()
  cur.execute(cmd )
  row=cur.fetchone()
  if row[0]==None :
    en=1001
  else:
    en=row[0]+1
  cmd="insert into emp values(%s,%s,%s,%s,%s)"
  curx=conn.cursor()
  curx.execute(cmd ,[en,nm,jb,sl,dn] )
  print('Record Inserted with empno',en)
  conn.commit()
  conn.close()
except Exception as e:
  print("Error:",e)

Enter Name: Gopal Pandey
Enter Job: manager
Enter Salary: 45000
Enter DeptNo: 20
Record Inserted with empno 1002

CCITcoder

Introduction to Python

Conditional statements

Iterative statements

Functions

OOPs

Collection

Module and Exception

DateBase

Inherintance

GUI

Introduction to Python

Conditional statements

Iterative statements

Functions

OOPs

Collection

Module and Exception

DateBase

Inherintance

GUI

Class MySQLCursor

Class MySQLCursor